SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.paObtenerCamposDeLaVista

@ID INT,
@IDTablaSubyacente INT = NULL

AS
SET NOCOUNT ON
SELECT COL.Colid [ID], COL.Name Nombre, 
CASE WHEN COL.Colid = 1 AND COL.Name = 'ID' THEN 1 ELSE 0 END EsClavePrimaria,
TIP.Name TipoDeDatoDelUsuario, TIP2.Name TipoDeDato, COL.IsNullable AdmiteNulo, COL.Length Longitud,
CASE TIP.Name WHEN 'int' THEN 10 WHEN 'datetime' THEN 10  WHEN 'bigint' THEN 11 ELSE COL.Length END LongitudDeValidacion,  
CASE COL.Status WHEN 128 THEN 1 ELSE 0 END EsIdentidad, NULL Descripcion, NULL ValorPorDefecto,
0 EsClaveForanea, NULL VistaReferencia,  
CASE PU.ProtectType WHEN 205 THEN 1 ELSE 0 END PermisoUpdate,
'V' Origen
FROM SYSCOLUMNS COL
INNER JOIN SYSTYPES TIP ON COL.XUserType = TIP.XUserType
INNER JOIN SYSTYPES TIP2 ON TIP2.XUserType = COL.XType
INNER JOIN 
	(
	SELECT P.Id, C.Name Nombre
	FROM SYSPROTECTS P
	INNER JOIN SYSCOLUMNS C ON C.Id = P.Id
	WHERE (IS_MEMBER(USER_NAME(P.Uid)) = 1 OR P.Grantor = USER_ID()) AND
	P.ProtectType = 205 AND P.Action = 193 AND 
		(((CONVERT(TINYINT, SUBSTRING(P.Columns, 1, 1)) & 1) = 0 AND (CONVERT(INT, SUBSTRING(P.Columns, C.ColId / 8 + 1, 1)) & POWER(2, C.ColId & 7)) != 0) OR
                    	((CONVERT(TINYINT, SUBSTRING(P.Columns, 1, 1)) & 1) != 0 AND (CONVERT(INT, SUBSTRING(P.Columns, C.ColId / 8 + 1, 1)) & POWER(2, C.Colid & 7)) = 0)) AND 
                     	0 != (PERMISSIONS(P.Id, C.Name) & 1)
	) PS ON PS.Nombre = COL.Name AND PS.Id = COL.Id
LEFT OUTER JOIN 
	(
	SELECT P.Id, C.Name Nombre, P.ProtectType
	FROM SYSPROTECTS P
	INNER JOIN SYSCOLUMNS C ON C.Id = P.Id
	WHERE (IS_MEMBER(USER_NAME(P.Uid)) = 1 OR P.Grantor = USER_ID()) AND
	P.ProtectType = 205 AND p.action = 197 AND 
		(((CONVERT(TINYINT, SUBSTRING(P.Columns, 1, 1)) & 1) = 0 AND (CONVERT(INT, SUBSTRING(P.Columns, C.ColId / 8 + 1, 1)) & POWER(2, C.ColId & 7)) != 0) OR
                    	((CONVERT(TINYINT, SUBSTRING(P.Columns, 1, 1)) & 1) != 0 AND (CONVERT(INT, SUBSTRING(P.Columns, C.ColId / 8 + 1, 1)) & POWER(2, C.Colid & 7)) = 0)) AND 
                     	0 != (PERMISSIONS(P.Id, C.Name) & 2)
	) PU ON PU.Nombre = COL.Name AND PU.Id = COL.Id
WHERE COL.Id = @ID

UNION

SELECT COL.Colid [ID], COL.Name Nombre, 
0 EsClavePrimaria,
TIP.Name TipoDeDatoDelUsuario, TIP.Name TipoDeDato, COL.IsNullable AdmiteNulo,
COL.Length Longitud, COL.Length LongitudDeValidacion,  
COL.Status EsIdentidad, PRO.Value Descripcion, COM.Text ValorPorDefecto,
CASE WHEN CR.VistaReferencia IS NULL THEN 0 ELSE 1 END EsClaveForanea, 
CR.VistaReferencia, 0 PermisoUpdate, 
'U' Origen
FROM SYSCOLUMNS COL
INNER JOIN SYSTYPES TIP ON COL.XUserType = TIP.XUserType
INNER JOIN SYSTYPES TIP2 ON TIP2.XUserType = COL.XType
LEFT OUTER JOIN SYSPROPERTIES PRO ON COL.Id = PRO.Id AND COL.ColId = PRO.SmallId
LEFT OUTER JOIN
	(
	SELECT 'vwr' + RIGHT(OBJ.Name, LEN(OBJ.Name) - 3) VistaReferencia, FK.FKeyId, FK.FKey
	FROM SYSOBJECTS OBJ
	INNER JOIN SYSFOREIGNKEYS FK ON OBJ.Id = FK.RKeyId
	) CR ON CR.FKeyId = COL.Id AND CR.FKey = COL.ColId
LEFT OUTER JOIN SYSCOMMENTS COM ON COL.CDefault = COM.Id
WHERE COL.Id = @IDTablaSubyacente AND (PRO.Value IS NOT NULL OR CR.VistaReferencia IS NOT NULL OR COM.Text IS NOT NULL)
ORDER BY Origen, COL.Colid
--WHERE COL.Id = @IDTablaSubyacente AND (PRO.Value IS NOT NULL OR CR.VistaReferencia IS NOT NULL)
--ORDER BY Origen, COL.Colid
GO
GRANT EXECUTE ON  [dbo].[paObtenerCamposDeLaVista] TO [CentroDeDesignaciones]
GRANT EXECUTE ON  [dbo].[paObtenerCamposDeLaVista] TO [public]
GRANT EXECUTE ON  [dbo].[paObtenerCamposDeLaVista] TO [SoloVer]
GO
